package sql.mysql;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.nio.file.attribute.UserDefinedFileAttributeView;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * Created by :Infaraway
 * DATE : 2017/3/6
 * Time : 15:51
 * Funtion :
 */
public class DBUtilsTest {

    //1 创建QueryRuuner实现类
    private QueryRunner queryRunner = new QueryRunner();


    /**
     * ScalarHandler: 把结果集转为一个数值(可以是任意基本数据类型和字符串， Date 等)返回
     */
    @Test
    public void testScalarHandler(){
        Connection connection = null;

        try {
            connection = MysqlUtils.getConnection();
            String sql = "select username from t_user";

            Object result = queryRunner.query(connection,
                    sql, new ScalarHandler());

            System.out.println(result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * MapListHandler: 将结果集转为一个 Map 的 List
     * Map 对应查询的一条记录: 键: SQL 查询的列名(不是列的别名), 值: 列的值.
     * 而 MapListHandler: 返回的多条记录对应的 Map 的集合.
     */
    @Test
    public void testMapListHandler(){
        Connection connection = null;

        try {
            connection = MysqlUtils.getConnection();
            String sql = "select * from t_user";

            List<Map<String, Object>> result = queryRunner.query(connection,
                    sql, new MapListHandler());

            System.out.println(result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * MapHandler: 返回 SQL 对应的第一条记录对应的 Map 对象.
     * 键: SQL 查询的列名(不是列的别名), 值: 列的值.
     */
    @Test
    public void testMapHandler(){
        Connection connection = null;

        try {
            connection = MysqlUtils.getConnection();
            String sql = "select * from t_user";

            Map<String, Object> result = queryRunner.query(connection,
                    sql, new MapHandler());

            System.out.println(result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * BeanListHandler: 把结果集转为一个 List, 该 List 不为 null, 但可能为
     * 空集合(size() 方法返回 0)
     * 若 SQL 语句的确能够查询到记录, List 中存放创建 BeanListHandler 传入的 Class
     * 对象对应的对象.
     */
    @Test
    public void testBeanListHandler(){
        Connection connection = null;

        try {
            connection = MysqlUtils.getConnection();
            String sql = "select * from t_user";

            List<User> userList = queryRunner.query(connection,sql, new BeanListHandler<User>(User.class));

            System.out.println(userList);
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * BeanHandler: 把结果集的第一条记录转为创建 BeanHandler 对象时传入的 Class
     * 参数对应的对象.
     */
    @Test
    public void testBeanHanlder(){
        Connection connection = null;

        try {
            connection = MysqlUtils.getConnection();
            String sql = "select * from t_user WHERE uid >= ?";

            User user = queryRunner.query(connection, sql, new BeanHandler<User>(User.class), 5);

            System.out.println(user.toString());
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    @Test
    public void testQuery(){

        //2 使用期update方法
        String sql = "select * from t_user";
        Connection connection = null;

        try {
            connection = MysqlUtils.getConnection();
            /**
             * query方法的返回值取决于其ResultSetHandler参数的handle方法的返回值
             */
            List<User> list = queryRunner.query(connection, sql, new BeanListHandler<User>(User.class));
            System.out.println(list.size());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Test
    public void testUpdate(){

        //2 使用期update方法
        String sql = "update t_user set password = ? where username = ?";
        Connection connection = null;

        try {
            connection = MysqlUtils.getConnection();
            queryRunner.update(connection, sql,"cheng", "zhong");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

}
